import json
# from icecream import ic
import pandas as pd
from openpyxl.styles import Alignment, Font
from openpyxl.styles import PatternFill
from openpyxl import Workbook, load_workbook
from datetime import datetime
from openpyxl.utils import get_column_letter
from openpyxl.styles import Border, Side

from gen_info_dic import gen_info_dic


class Color:
    def __init__(self):
        self.gray = PatternFill(
            start_color='C0C0C0',
            end_color='C0C0C0',
            fill_type='solid')
        self.blue = PatternFill(
            start_color='D9E1F2',
            end_color='D9E1F2',
            fill_type='solid')
        self.yellow = PatternFill(
            start_color='FFFF00',
            end_color='FFFF00',
            fill_type='solid')


border_style = Border(
    left=Side(border_style='thin', color='000000'),
    right=Side(border_style='thin', color='000000'),
    top=Side(border_style='thin', color='000000'),
    bottom=Side(border_style='thin', color='000000')
)

def decorate_DPAT(sheet, border_style):
    color = Color()
    for col in range(1, 15):
        for row in range(1, 3):
            cell = sheet.cell(row=row, column=col)
            if row == 1:
                sheet.row_dimensions[row].height = 14.25
                cell.border = border_style
            else:
                sheet.row_dimensions[row].height = 57
            cell.fill = color.blue
            # 对其方式
            cell.alignment = Alignment(
                horizontal='center',
                vertical='center',
                wrap_text=True)
            cell.font = Font(name='宋体', size=11, bold=True)
    sheet.merge_cells('C1:F1')
    sheet.merge_cells('G1:H1')
    sheet.merge_cells('K1:L1')
    sheet.merge_cells('M1:N1')
    sheet['C1'] = 'Dynamic Limit'
    sheet['G1'] = 'Static Limit'
    sheet['K1'] = 'Continuity Check'
    sheet['M1'] = 'Normalize Check'


def decorate_SPAT(sheet, border_style):
    color = Color()
    for col in range(1, 16):
        for row in range(1, 3):
            cell = sheet.cell(row=row, column=col)
            if row == 1:
                sheet.row_dimensions[row].height = 14.25
                cell.border = border_style
            else:
                sheet.row_dimensions[row].height = 57
            cell.fill = color.blue
            cell.alignment = Alignment(
                horizontal='center',
                vertical='center',
                wrap_text=True)
            cell.font = Font(name='宋体', size=11, bold=True)

    sheet.merge_cells('C1:G1')
    sheet.merge_cells('H1:I1')
    sheet.merge_cells('L1:M1')
    sheet.merge_cells('N1:O1')
    sheet['C1'] = 'Dynamic Limit'
    sheet['H1'] = 'Static Limit'
    sheet['L1'] = 'Continuity Check'
    sheet['N1'] = 'Normalize Check'

def decorate_Correlation(sheet, border_style):
    color = Color()
    for col in range(1, 8):
        for row in range(1, 3):
            cell = sheet.cell(row=row, column=col)
            if row == 1:
                sheet.row_dimensions[row].height = 14.25
                cell.border = border_style
            else:
                sheet.row_dimensions[row].height = 57
            cell.fill = color.blue
            cell.alignment = Alignment(
                horizontal='center',
                vertical='center',
                wrap_text=True)
            cell.font = Font(name='宋体', size=11, bold=True)

    sheet.merge_cells('D1:G1')
    sheet['D1'] = 'Bivariate Correlation Limit'

def decorate_rule_detail(info_dic, re_file_path):
    existing_wb = load_workbook(re_file_path)
    rule_key_alias_mapping = info_dic['rule_map']
    for alias, key in rule_key_alias_mapping.items():
        if key == 'DPAT':
            sheet_name = f'{alias} Detail'
            sheet = existing_wb[sheet_name]
            decorate_DPAT(sheet, border_style)
        if key == 'SPAT':
            sheet_name = f'{alias} Detail'
            sheet = existing_wb[sheet_name]
            decorate_SPAT(sheet, border_style)
        if key == 'CORRELATION_RULE':
            sheet_name = f'{alias} Detail'
            sheet = existing_wb[sheet_name]
            decorate_Correlation(sheet, border_style)
    existing_wb.save(re_file_path)


if __name__ == '__main__':
    from get_sql_res import gen_run_detail
    rule_run_record_id = 7088
    df_run_detail = gen_run_detail(rule_run_record_id)
    current_time = datetime.now().strftime("%m_%d_%H_%M")
    re_file_path = f"res/file_{current_time}.xlsx"
    sheet = 'Summary'
    info_dic = gen_info_dic(df_run_detail)
    ##
    write_in(rule_run_record_id, re_file_path)
    decorate(re_file_path, sheet, info_dic)
